﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using Marbles.Core.Settings;

namespace Marbles.Core.Connectors
{
    internal class SqlDataConnection : IDataConnection
    {

        public string ProviderName { get { return "SQL Server"; } }

        private string _connectionstring = string.Empty;

        public SqlDataConnection()
        {
            DataConnectionSettings settings = SettingsManager.Connection;
            _connectionstring = BuildConnectionString(settings.Host, settings.Username, settings.Password, settings.Schema);
            if (settings.ProviderName != "sqlserver")
            {
                throw new InvalidOperationException("Expecting a SQL Server Provider in Connection String");
            }
        }

        internal SqlConnection CreateConnection()
        {
            return new SqlConnection(_connectionstring);
        }

        public DataTable FetchDataTable(Command Command)
        {
            SqlConnection connection = CreateConnection();
            connection.Open();
            SqlCommand cmd = (SqlCommand)CreateSqlCommand(connection, Command);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            connection.Close();
            return dt;
        }

        public DataSet FetchDataSet(Command Command)
        {
            SqlConnection connection = CreateConnection();
            connection.Open();
            SqlCommand cmd = (SqlCommand)CreateSqlCommand(connection, Command);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            connection.Close();
            return ds;
        }

        public Int32 ExecuteCommand(Command Command)
        {
            SqlConnection connection = CreateConnection();
            connection.Open();
            SqlCommand cmd = (SqlCommand)CreateSqlCommand(connection, Command);
            Int32 result = cmd.ExecuteNonQuery();
            connection.Close();
            return result;
        }


        public IDbCommand CreateSqlCommand(IDbConnection Connection, Command Command)
        {
            SqlCommand cmd = new SqlCommand(Command.CommandText, (SqlConnection)Connection);
            foreach (string key in Command.Parameters.Keys)
            {
                cmd.Parameters.AddWithValue(key, Command.Parameters[key] == null ? DBNull.Value : Command.Parameters[key]);
            }

            return cmd;
        }

        public bool Ping()
        {
            SqlConnection connection = CreateConnection();
            try
            {
                connection.Open();
                connection.Close();
            }
            catch
            {
                return false;
            }

            return true;
        }

        public string BuildConnectionString(string Host, string Username, string Password, string Database)
        {
            return string.Format("SERVER={0};DATABASE={1};USER ID={2};PWD={3};", Host, Database, Username, Password);
        }

    }
}